# -*- coding: UTF-8 -*-
import sqlite3
from config.logging_config import logger
from config.template_config import SqliteConfig


# SQLite 数据库操作类
class Sqlite3Helper(object):
    def __init__(self, db_path:str):
        self.db_path = db_path
    def open(self):
        # 去连接池中获取一个连接
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        return conn, cursor
    def close(self, conn, cur):
        cur.close()
        # 将连接放回到连接池，并不会关闭连接，当线程终止时，连接自动关闭
        conn.close()
    def get_columns(self, description):
        column_list = []
        if description is None or len(description) == 0:
            return column_list
        for column in description:
            column_list.append(column[0])
        return column_list
    def package_data(self, column_list, result):
        data_list = []
        if column_list is None or len(column_list) == 0:
            return data_list
        if result is None or len(result) == 0:
            return data_list
        if isinstance(result, tuple):
            data_dict = {}
            for index, item in enumerate(result):
                column = column_list[index].lower()
                data_dict[column] = item
            data_list.append(data_dict)
            return data_list
        if isinstance(result, list):
            for data in result:
                data_dict = {}
                for index, item in enumerate(data):
                    column = column_list[index].lower()
                    data_dict[column] = item
                data_list.append(data_dict)
            return data_list
        return data_list
    # 获取所有数据
    # SQL语句的占位参数
    # 查询结果
    def get_list(self, sql, args=None):
        conn, cursor = self.open()
        try:
            cursor.execute(sql, args or [])
            result = cursor.fetchall()
            column_list = self.get_columns(cursor.description)
            return self.package_data(column_list, result)
        except Exception as e:
            logger.error(e)
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)
        # 获取单条数据
        # return: 查询结果

    def get_one(self, sql, args=None):
        conn, cursor = self.open()
        try:
            cursor.execute(sql, args or [])
            column_list = self.get_columns(cursor.description)
            result = cursor.fetchone()
            return self.package_data(column_list, result)
        except Exception as e:
            logger.error(e)
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)


    def save(self, sql, args=None):
        conn, cursor = self.open()
        try:
            cursor.execute(sql, args or [])
            conn.commit()
        except Exception as e:
            conn.rollback()
            logger.error(e)
            return 0
        else:
            return 1
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)

        # 批量操作

    def batch_run(self, sql, args=None):
        conn, cursor = self.open()
        try:
            result = cursor.executemany(sql, args or [])
            conn.commit()
            return result
        except Exception as e:
            conn.rollback()
            logger.error(e)
            return 0
        else:
            return 1
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)

    def execute_sql(self, sql):
        conn, cursor = self.open()
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            conn.rollback()
            logger.error(e)
            return 0
        else:
            return 1
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)
    def gen_snowflake_id(self):
        return self.snowflake.get_snowflake_id()
    def query_column_type(self,table_name, column_filter_list=None):
        query_column_sql_template = SqliteConfig.QUERY_COLUMN_TYPE_SQL
        query_column_sql = query_column_sql_template.format(table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_dict = {}
        if column_filter_list is not None and len(column_filter_list) > 0:
            for item in result:
                column_name = item.get("column_name").lower()
                if column_name in column_filter_list:
                    column_dict[column_name + "_data_type"] = item.get("data_type")
                    column_dict[column_name + "_is_nullable"] = item.get("is_nullable")
                    column_dict[column_name + "_column_default"] = item.get("column_default")
                    column_dict[column_name + "_ordinal_position"] = item.get("ordinal_position")
        else:
            for item in result:
                column_name = item.get("name").lower()
                column_dict[column_name + "_data_type"] = item.get("type")
                column_dict[column_name + "_is_nullable"] = item.get("notnull")
                column_dict[column_name + "_column_default"] = item.get("dflt_value")
                column_dict[column_name + "_ordinal_position"] = item.get("cid")

        logger.info("字段类型映射生成成功------------\n" + str(column_dict))
        return column_dict

    def generate_insert_statement(self, table_name, column_filter_list=None):
        query_column_sql_template = SqliteConfig.QUERY_COLUMN_SQL
        query_column_sql = query_column_sql_template.format(table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_list_map = map(lambda item: item["name"], result)
        column_list = None
        if column_filter_list is not None and len(column_filter_list) > 0:
            column_list_map_list = list(column_list_map)
            column_list_filter = filter(lambda item: item.lower() in column_filter_list, column_list_map_list)
            column_list = list(column_list_filter)
        else:
            column_list = list(column_list_map)

        placeholders = ', '.join(f'`{column.lower()}`' for column in column_list)
        values = ', '.join("{" + f'{column.lower()}' + "}" for column in column_list)
        sql_template = f"insert into `{table_name}` ({placeholders}) values ({values});"
        format_values = ', '.join(
            f'{column.lower()}' + "= deal_data(item,'" + f'{column.lower()}' + "',column_type_dict)" for column in
            column_list)
        replace_template = "sql_template.format(" + format_values + ")"

        logger.info("语句生成成功---------------\n" + sql_template)
        logger.info("替换模板生成成功------------\n" + replace_template)
        logger.info("-------------------------")
        return sql_template, replace_template

    def generate_batch_insert_statement(self, table_name, column_filter_list=None):
        query_column_sql_template = SqliteConfig.QUERY_COLUMN_SQL
        query_column_sql = query_column_sql_template.format(table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_list_map = map(lambda item: item["name"], result)
        column_list = None
        if column_filter_list is not None and len(column_filter_list) > 0:
            column_list_map_list = list(column_list_map)
            column_list_filter = filter(lambda item: item.lower() in column_filter_list, column_list_map_list)
            column_list = list(column_list_filter)
        else:
            column_list = list(column_list_map)

        placeholders = ', '.join(f'`{column.lower()}`' for column in column_list)
        values_template = "( " + ', '.join("{" + f'{column.lower()}' + "}" for column in column_list) + " )"
        sql_template = f"insert into `{table_name}` ({placeholders}) values "
        format_values = ', '.join(
            f'{column.lower()}' + "= deal_data(item,'" + f'{column.lower()}' + "',column_type_dict)" for column in
            column_list)
        replace_template = "values_template.format(" + format_values + ")"

        logger.info("语句生成成功---------------\n" + sql_template)
        logger.info("数据模板生成成功---------------\n" + values_template)
        logger.info("替换模板生成成功------------\n" + replace_template)
        logger.info("-------------------------")
        return sql_template, values_template, replace_template

    def generate_params_template_statement(self,table_name, column_filter_list=None):
        query_column_sql_template = SqliteConfig.QUERY_COLUMN_SQL
        query_column_sql = query_column_sql_template.format(table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_list_map = map(lambda item: item["name"], result)
        column_list = None
        if column_filter_list is not None and len(column_filter_list) > 0:
            column_list_map_list = list(column_list_map)
            column_list_filter = filter(lambda item: item.lower() in column_filter_list, column_list_map_list)
            column_list = list(column_list_filter)
        else:
            column_list = list(column_list_map)
        column_maps = []
        for column in column_list:
            column_maps.append("data_dict['" + f'{column.lower()}' + "']= item." + f'{column.lower()}')
        params_template = "data_dict = {} \n" + '\n '.join(column_maps)
        logger.info("参数模板生成成功------------\n" + params_template)